Skip to main content

Install and config PostgREST

The following parameters are used in the article, and have been created properly:

  • Database: postgres
  • Account used by postgrest to connect to the databse: web_anon
  • Schema: api

Installation​

Official Docker web page: https://hub.docker.com/r/postgrest/postgrest/

# Run the server
docker run --rm --net=host \
  -e PGRST_DB_URI="postgres://app_user:password@localhost/postgres" \
  postgrest/postgrest

DB Authorization​

Reference: https://postgrest.org/en/stable/explanations/db_authz.html, https://postgrest.org/en/stable/tutorials/tut0.html#step-3-install-postgrest

Add read-only access​

Postgres configuration​

Assuming a table todos is created under the schema:

create table api.todos (
  id serial primary key,
  done boolean not null default false,
  task text not null,
  due timestamptz
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');

Grant the user select permission of the table

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;

It's also a good practice to use a dedicated role for the connection rather than postgres. So the following query assign the user to authenticator

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;

Postgrest configuration​

db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"
db-schemas = "api"
db-anon-role = "web_anon"

The GET should be working

curl http://localhost:3000/todos

Allow write access​